package cn.edu.zhku.citrusmonitor.illness.dao;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.BeanProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.RowProcessor;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.edu.zhku.citrusmonitor.domain.TbIllness;
import cn.edu.zhku.citrusmonitor.domain.TbUser;
import cn.edu.zhku.citrusmonitor.util.C3P0Util;
import cn.edu.zhku.citrusmonitor.util.StringUtil;

public class IllnessDaoImpl implements IllnessDao {
	BeanProcessor bean = new GenerousBeanProcessor();
	RowProcessor processor = new BasicRowProcessor(bean);

	@Override
	public int modifyIllnessByIllnessBean(TbIllness illness) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		List<Object> param=new ArrayList<>();
		String sql = "update illness set illness_id=?";	//这里illness_id的作用是让后面的sql语句能够整齐并兼容
		param.add(illness.getIllnessId());
		
		if(StringUtil.isNotEmpty(illness.getUserName())) {
			sql +=",user_name=?";
			param.add(illness.getUserName());
		}
		if(StringUtil.isNotEmpty(illness.getTemperature())) {
			sql +=",temperature=?";
			param.add(illness.getTemperature());
		}
		if(StringUtil.isNotEmpty(illness.getAdultPestNum())) {
			sql +=",adult_pest_num=?";
			param.add(illness.getAdultPestNum());
		}
		if(StringUtil.isNotEmpty(illness.getLarvaNum())) {
			sql +=",larva_num=?";
			param.add(illness.getLarvaNum());
		}
		if(StringUtil.isNotEmpty(illness.getEggNum())) {
			sql +=",egg_num=?";
			param.add(illness.getEggNum());
		}
		if(StringUtil.isNotEmpty(illness.getDetail())) {
			sql +=",detail=?";
			param.add(illness.getDetail());
		}
		if(StringUtil.isNotEmpty(illness.getPhoneNum())) {
			sql +=",phone_num=?";
			param.add(illness.getPhoneNum());
		}
		if(StringUtil.isNotEmpty(illness.getCreateTime())) {
			sql += ",create_time=?";
			param.add(illness.getCreateTime());
		}
		sql +=" where illness_id=?";
		param.add(illness.getIllnessId());
		return qr.update(sql, param.toArray());	//将列表param转换成字符串
	}

	@Override
	public int addIllnwssByIllnessBean(TbIllness illness, TbUser user) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "insert into illness(illness_id,user_id,user_name,temperature,adult_pest_num,larva_num,egg_num,detail,phone_num,create_time) values(?,?,?,?,?,?,?,?,?,?)";
		return qr.update(sql,illness.getIllnessId(), user.getUserId(), illness.getUserName(), illness.getTemperature(), illness.getAdultPestNum(), illness.getLarvaNum(), illness.getEggNum(), illness.getDetail(), illness.getPhoneNum(), illness.getCreateTime());
	}

	@Override
	public int removeIllnessByIllnessId(String illnessId) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="delete from illness where illness_id=?";
		
		return qr.update(sql, illnessId);
	}

	@Override
	public List<TbIllness> getIllnessById(String illnessId) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql  = "select * from illness where illness_id=?";
		List<TbIllness> list = qr.query(sql, new BeanListHandler<TbIllness>(TbIllness.class,processor), illnessId);
		return list;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public long getCount() throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql = "select count(*) from illness ";
		long l = (long) qr.query(sql, new ScalarHandler(1));
		return l;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public List<TbIllness> getIllnessBeanList(int currentPage, int pageSize, TbIllness illness) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from illness";
		ArrayList param=new ArrayList();
		sql += " limit ?,?";
		param.add((currentPage-1)*pageSize);
		param.add(pageSize);
		List<TbIllness> list = qr.query(sql, new BeanListHandler<TbIllness>(TbIllness.class,processor), param.toArray());
		return list;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List<TbIllness> getIllnessDetailBeanList(Integer currentPage, Integer pageSize) throws SQLException {
		QueryRunner qr=new QueryRunner(C3P0Util.getDataSource());
		String sql="select illness_id,user_id,user_name,temperature,adult_pest_num,larva_num,egg_num,detail,phone_num,create_time from illness";
		ArrayList param = new ArrayList();
		sql += " limit ?,?";
		param.add((currentPage-1)*pageSize);
		param.add(pageSize);
		List<TbIllness> list = qr.query(sql, new BeanListHandler<TbIllness>(TbIllness.class,processor), param.toArray());
		return list;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public Long searchPestCountByWord(String word) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select count(*) from illness where";
		ArrayList param=new ArrayList();
		sql+=" temperature like ?";
		param.add("%"+word+"%");
		sql+=" or adult_pest_num like ?";
		param.add("%"+word+"%");
		sql+=" or egg_num like ?";
		param.add("%"+word+"%");
		sql+=" or larva_num like ?";
		param.add("%"+word+"%");
		sql+=" or phone_num like ?";
		param.add("%"+word+"%");
		Long l = (Long) qr.query(sql, new ScalarHandler(1), param.toArray());
		return l;
	}

	@SuppressWarnings({ "rawtypes", "unchecked" })
	@Override
	public List<TbIllness> searchPestByWord(Integer currentPage, Integer pageSize, String word) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="select * from illness where";
		ArrayList param=new ArrayList();
		sql+=" temperature like ?";
		param.add("%"+word+"%");
		sql+=" or adult_pest_num like ?";
		param.add("%"+word+"%");
		sql+=" or egg_num like ?";
		param.add("%"+word+"%");
		sql+=" or larva_num like ?";
		param.add("%"+word+"%");
		sql+=" or phone_num like ?";
		param.add("%"+word+"%");
		sql+=" limit ?,?";
		param.add((currentPage-1)*pageSize);
		param.add(pageSize);
		List<TbIllness> list = qr.query(sql, new BeanListHandler<TbIllness>(TbIllness.class,processor), param.toArray());
		return list;
	}

	@Override
	public int[] delSelect(String[] ids) throws SQLException {
		QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
		String sql="delete from illness where illness_id=?";
		Object[][] params = new Object[ids.length][1];	//高维也就是行数 确定执行sql语句的次数，低维也就是列数是给?赋值
		
		for(int i = 0; i < ids.length; i++) {	//循环行数,决定SQL语句执行的次数
			params[i][0] = ids[i];	//给低维也就是列数“？”赋值，每行只给一列赋值，决定每条SQL语句的参数个数
		}
		return qr.batch(sql, params);
	}

}
